package ice.dao.util;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import cube.utils.SQLUtil;
import cube.utils.TimeUtil;

/**
 * 查询结果对象
 * 
 * @author abiao
 */
public abstract class QueryResult {

    /**
     * 数据操作工具
     */
    private DBUtil dbUtil;

    /**
     * 查询SQL
     */
    private String sql;

    /**
     * 查询SQL参数
     */
    private Object[] params;
    private Connection conn = null;
    private PreparedStatement stmt = null;
    private ResultSet rs = null;
    private ResultSetMetaData meta = null;

    /**
     * 查询结果
     */
    public QueryResult(DBUtil dbUtil, String sql, Object[] params) {
        this.dbUtil = dbUtil;
        this.sql = sql;
        this.params = params;
        initQueryHandle();
    }

    /**
     * 获取查询数据列表
     */
    public List<TableRow> queryList() {
        List<TableRow> rowList = new ArrayList<TableRow>();
        try {
            int colSize = getColumnSize();
            while (rs.next()) {
                TableRow row = new TableRow();
                for (int i = 1; i <= colSize; i++) {
                    row.put(getRowKey(i), getQueryData(i));
                }
                rowList.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeQueryResultHandle();
        }
        return rowList;
    }

    /**
     * 获取列名
     */
    protected String getColumnName(int column) throws SQLException {
        return meta.getColumnName(column).toLowerCase();
    }

    /**
     * 初始化查询操作
     */
    private void initQueryHandle() {
        try {
            this.conn = dbUtil.getConnection();
            this.stmt = dbUtil.getPrepStmt(conn, sql);
            SQLUtil.setSQLParams(stmt, params);
            this.rs = stmt.executeQuery();
            meta = rs.getMetaData();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取列数
     */
    private int getColumnSize() throws SQLException {
        return meta.getColumnCount();
    }

    /**
     * 获取列类型
     */
    private String getColumnType(int column) throws SQLException {
        return meta.getColumnTypeName(column).toLowerCase();
    }

    /**
     * 获取查询数据
     */
    private Object getQueryData(int columnIndex) throws SQLException {
        String colType = getColumnType(columnIndex);
        if (isBlob(colType)) {
            return getBlobData(columnIndex);
        } else if (isByteArray(colType)) {
            return getBytes(columnIndex);
        } else if (isDate(colType)) {
            return getDateTime(columnIndex);
        } else {
            return getString(columnIndex);
        }
    }

    /**
     * 判断类型
     */
    private boolean isBlob(String colType) {
        return colType.contains("blob");
    }

    /**
     * 判断类型
     */
    private boolean isByteArray(String colType) {
        return colType.contains("bytea");
    }

    /**
     * 判断类型
     */
    private boolean isDate(String colType) {
        return colType.contains("date") || colType.contains("datetime") || colType.contains("time");
    }

    /**
     * 获取相应类型数据
     */
    private byte[] getBlobData(int columnIndex) throws SQLException {
        Blob blob = rs.getBlob(columnIndex);
        byte[] blobData = null;
        if (blob != null) {
            blobData = blob.getBytes(1L, (int) blob.length());
        }
        return blobData;
    }

    /**
     * 获取相应类型数据
     */
    private byte[] getBytes(int columnIndex) throws SQLException {
        return rs.getBytes(columnIndex);
    }

    /**
     * 获取相应类型数据
     */
    private String getDateTime(int columnIndex) throws SQLException {
        Timestamp stamp = rs.getTimestamp(columnIndex);
        String dateTime = null;
        if (stamp != null) {
            dateTime = TimeUtil.getSQLTime(stamp);
        }
        return dateTime;
    }

    /**
     * 获取相应类型数据
     */
    private String getString(int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    /**
     * 关闭SQL连接
     */
    protected void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集
     */
    protected void closeResultSet() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭语句处理
     */
    protected void closeStatement() {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取查询数据关键字
     */
    protected abstract String getRowKey(int columnIndex) throws SQLException;

    /**
     * 关闭查询操作
     */
    protected abstract void closeQueryResultHandle();
}
